package com.yanqu.road.dao.impl.recharge;

import com.yanqu.road.dao.db.DbNameStrategy;
import com.yanqu.road.dao.db.DbParameter;
import com.yanqu.road.dao.impl.TempDao;
import com.yanqu.road.entity.recharge.RechargeMultipleInfo;
import com.yanqu.road.entity.recharge.RechargeOrder;
import com.yanqu.road.entity.enums.eOrderStatus;
import com.yanqu.road.utils.string.StringUtils;
import org.apache.commons.lang.time.DateUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

public class RechargeDaoImpl extends TempDao {

	public List<RechargeOrder> getAllRechargeOrderList(long userId) {
		ResultSet rs = null;
		CallableStatement call = null;
		List<RechargeOrder> dataList = new ArrayList<>();
		String sql = "select * from t_u_recharge_detail where userId =?";
		try {
			getDb().setConnName(DbNameStrategy.getUserDb());
			call = getDb().getConnection().prepareCall(sql);
			call.setLong(1, userId);
			rs = call.executeQuery();
			while (rs.next()) {
				dataList.add(initRechargeOrder(rs));
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			getDb().closeConnection(call, rs);
		}
		return dataList;
	}

	public List<RechargeOrder> getLastMonthRechargeOrderList(long userId) {
		ResultSet rs = null;
		CallableStatement call = null;
		List<RechargeOrder> dataList = new ArrayList<>();
		String sql = "select * from t_u_recharge_detail where userId =? and createTime > ?";
		try {
			getDb().setConnName(DbNameStrategy.getUserDb());
			call = getDb().getConnection().prepareCall(sql);
			call.setLong(1, userId);
			call.setDate(2, new Date(DateUtils.addMonths(new java.util.Date(),-1).getTime()));
			rs = call.executeQuery();
			while (rs.next()) {
				dataList.add(initRechargeOrder(rs));
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			getDb().closeConnection(call, rs);
		}
		return dataList;
	}

	public boolean addRechargeOrder(RechargeOrder rechargeOrder) {
		PreparedStatement call = null;
		String sql = "INSERT INTO t_u_recharge_detail(userId,orderNo,amount,orderStatus,createTime,updateTime,mallId,preChargeType,activityId," +
				"conditionId,returnIngots,addVipExp,shopType,mallName,mallMarkId,couponItemId,couponGoodsId) " +
                "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		try {
			getDb().setConnName(DbNameStrategy.getUserDb());
			call = getDb().getConnection().prepareStatement(sql);
			int i = 1;
			call.setLong(i++, rechargeOrder.getUserId());
			call.setString(i++, rechargeOrder.getOrderNo());
			call.setLong(i++, rechargeOrder.getAmount());
			call.setInt(i++, rechargeOrder.getOrderStatus().getValue());
			call.setTimestamp(i++, new Timestamp(System.currentTimeMillis()));
			call.setTimestamp(i++, new Timestamp(System.currentTimeMillis()));
			call.setInt(i++, rechargeOrder.getMallId());
			call.setInt(i++, rechargeOrder.getPreChargeType());
			call.setInt(i++, rechargeOrder.getActivityId());
			call.setInt(i++, rechargeOrder.getConditionId());
			call.setLong(i++, rechargeOrder.getReturnIngots());
			call.setInt(i++, rechargeOrder.getAddVipExp());
			call.setInt(i++, rechargeOrder.getShopType());
			call.setString(i++, rechargeOrder.getMallName());
			call.setInt(i++, rechargeOrder.getMallMarkId());
			call.setLong(i++, rechargeOrder.getCouponItemId());
			call.setInt(i++, rechargeOrder.getCouponGoodsId());
            return call.executeUpdate() > 0;
		} catch (SQLException e) {
			logger.error(Thread.currentThread().getStackTrace()[1].getMethodName() + ":", e);
		} finally {
			getDb().closeConnection(call, null);
		}
		return false;
	}

	public boolean addRechargeOrderFull(RechargeOrder rechargeOrder) {
		PreparedStatement call = null;
		String sql = "INSERT INTO t_u_recharge_detail(userId,orderNo,amount,orderStatus,createTime,updateTime,mallId,preChargeType,activityId," +
				"conditionId,returnIngots,addVipExp,shopType,mallName,mallMarkId,platformOrderNo,sdkOrderNo,payType,currency,realMoney,realCurrency," +
				"localMoney,localCurrency,ltrRate,newMoney,tax,productCoin,device,payChannel,orderType,couponMoney,productType,subscription," +
				"couponItemId,couponGoodsId,gid,appid,payMethod,packageId) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		try {
			getDb().setConnName(DbNameStrategy.getUserDb());
			call = getDb().getConnection().prepareStatement(sql);
			int i = 1;
			call.setLong(i++, rechargeOrder.getUserId());
			call.setString(i++, rechargeOrder.getOrderNo());
			call.setLong(i++, rechargeOrder.getAmount());
			call.setInt(i++, rechargeOrder.getOrderStatus().getValue());
			call.setTimestamp(i++, new Timestamp(System.currentTimeMillis()));
			call.setTimestamp(i++, new Timestamp(System.currentTimeMillis()));
			call.setInt(i++, rechargeOrder.getMallId());
			call.setInt(i++, rechargeOrder.getPreChargeType());
			call.setInt(i++, rechargeOrder.getActivityId());
			call.setInt(i++, rechargeOrder.getConditionId());
			call.setLong(i++, rechargeOrder.getReturnIngots());
			call.setInt(i++, rechargeOrder.getAddVipExp());
			call.setInt(i++, rechargeOrder.getShopType());
			call.setString(i++, rechargeOrder.getMallName());
			call.setInt(i++, rechargeOrder.getMallMarkId());
			call.setString(i++, rechargeOrder.getPlatformOrderNo());
			call.setString(i++, rechargeOrder.getSdkOrderNo());
			call.setInt(i++, rechargeOrder.getPayType());
			call.setString(i++, rechargeOrder.getCurrency());
			call.setString(i++, rechargeOrder.getRealMoney());
			call.setString(i++, rechargeOrder.getRealCurrency());
			call.setString(i++, rechargeOrder.getLocalMoney());
			call.setString(i++, rechargeOrder.getLocalCurrency());
			call.setString(i++, rechargeOrder.getLtrRate());
			call.setString(i++, rechargeOrder.getNewMoney());
			call.setString(i++, rechargeOrder.getTax());
			call.setLong(i++, rechargeOrder.getProductCoin());
			call.setInt(i++, rechargeOrder.getDevice());
			call.setString(i++, rechargeOrder.getPayChannel());
			call.setInt(i++, rechargeOrder.getOrderType());
			call.setString(i++, rechargeOrder.getCouponMoney());
			call.setString(i++, rechargeOrder.getProductType());
			call.setString(i++, rechargeOrder.getSubscription());
			call.setLong(i++, rechargeOrder.getCouponItemId());
			call.setInt(i++, rechargeOrder.getCouponGoodsId());
			call.setString(i++, rechargeOrder.getGid());
			call.setString(i++, rechargeOrder.getAppid());
			call.setString(i++, rechargeOrder.getPayMethod());
			call.setString(i++, rechargeOrder.getPackageId());
			return call.executeUpdate() > 0;
		} catch (SQLException e) {
			logger.error(Thread.currentThread().getStackTrace()[1].getMethodName() + ":", e);
		} finally {
			getDb().closeConnection(call, null);
		}
		return false;
	}

	public RechargeOrder getRechargeOrder(String orderNo) {
		ResultSet rs = null;
		CallableStatement call = null;
		String sql = "select * from t_u_recharge_detail where orderNo=?";
		try {
			getDb().setConnName(DbNameStrategy.getUserDb());
			call = getDb().getConnection().prepareCall(sql);
			call.setString(1, orderNo);
			rs = call.executeQuery();
			while (rs.next()) {
				RechargeOrder data = initRechargeOrder(rs);
				return data;
			}
		} catch (SQLException e) {
			logger.error(Thread.currentThread().getStackTrace()[1].getMethodName() + ":", e);
		} finally {
			getDb().closeConnection(call, rs);
		}
		return null;
	}

	public RechargeOrder getRechargeOrderBySdkOrderNo(String sdkOrderNo) {
		ResultSet rs = null;
		CallableStatement call = null;
		String sql = "select * from t_u_recharge_detail where sdkOrderNo=?";
		try {
			getDb().setConnName(DbNameStrategy.getUserDb());
			call = getDb().getConnection().prepareCall(sql);
			call.setString(1, sdkOrderNo);
			rs = call.executeQuery();
			while (rs.next()) {
				RechargeOrder data = initRechargeOrder(rs);
				return data;
			}
		} catch (SQLException e) {
			logger.error(Thread.currentThread().getStackTrace()[1].getMethodName() + ":", e);
		} finally {
			getDb().closeConnection(call, rs);
		}
		return null;
	}

	public boolean updateRechargeOrder(RechargeOrder rechargeOrder) {
		String sql = "UPDATE t_u_recharge_detail SET orderStatus=?,updateTime=?,platformOrderNo=?,sdkOrderNo=?,payType=?,currency=?,realMoney=?,"
				+ "realCurrency=?,localMoney=?,localCurrency=?,ltrRate=?,newMoney=?,tax=?,productCoin=?,device=?,payChannel=?,orderType=?,"
				+ "couponMoney=?,productType=?,subscription=?,couponItemId=?,couponGoodsId=? WHERE orderNo=? and orderStatus != 2";
		getDb().setConnName(DbNameStrategy.getUserDb());
		Map<Integer, DbParameter> para = new HashMap<>();
		int i = 1;
		para.put(i++, new DbParameter(Types.INTEGER, rechargeOrder.getOrderStatus().getValue()));
		para.put(i++, new DbParameter(Types.TIMESTAMP, new Timestamp(System.currentTimeMillis())));
		para.put(i++, new DbParameter(Types.NVARCHAR, rechargeOrder.getPlatformOrderNo()));
		para.put(i++, new DbParameter(Types.NVARCHAR, rechargeOrder.getSdkOrderNo()));
		para.put(i++, new DbParameter(Types.INTEGER, rechargeOrder.getPayType()));
		para.put(i++, new DbParameter(Types.NVARCHAR, rechargeOrder.getCurrency()));
		para.put(i++, new DbParameter(Types.NVARCHAR, rechargeOrder.getRealMoney()));
		para.put(i++, new DbParameter(Types.NVARCHAR, rechargeOrder.getRealCurrency()));
		para.put(i++, new DbParameter(Types.NVARCHAR, rechargeOrder.getLocalMoney()));
		para.put(i++, new DbParameter(Types.NVARCHAR, rechargeOrder.getLocalCurrency()));
		para.put(i++, new DbParameter(Types.NVARCHAR, rechargeOrder.getLtrRate()));
		para.put(i++, new DbParameter(Types.NVARCHAR, rechargeOrder.getNewMoney()));
		para.put(i++, new DbParameter(Types.NVARCHAR, rechargeOrder.getTax()));
		para.put(i++, new DbParameter(Types.BIGINT, rechargeOrder.getProductCoin()));
		para.put(i++, new DbParameter(Types.INTEGER, rechargeOrder.getDevice()));
		para.put(i++, new DbParameter(Types.NVARCHAR, rechargeOrder.getPayChannel()));
		para.put(i++, new DbParameter(Types.INTEGER, rechargeOrder.getOrderType()));
		para.put(i++, new DbParameter(Types.VARCHAR, rechargeOrder.getCouponMoney()));
		para.put(i++, new DbParameter(Types.VARCHAR, rechargeOrder.getProductType()));
		para.put(i++, new DbParameter(Types.VARCHAR, rechargeOrder.getSubscription()));
		para.put(i++, new DbParameter(Types.BIGINT, rechargeOrder.getCouponItemId()));
        para.put(i++, new DbParameter(Types.INTEGER, rechargeOrder.getCouponGoodsId()));
		para.put(i++, new DbParameter(Types.VARCHAR, rechargeOrder.getOrderNo()));

		return getDb().executeNoneQuery(sql, para) > -1;
	}

	public boolean updateRechargeOrderStatus(RechargeOrder rechargeOrder) {
		String sql = "UPDATE t_u_recharge_detail SET orderStatus=?,vipExp=? WHERE orderNo=?";
		getDb().setConnName(DbNameStrategy.getUserDb());
		Map<Integer, DbParameter> para = new HashMap<>();
		para.put(1, new DbParameter(Types.INTEGER, rechargeOrder.getOrderStatus().getValue()));
		para.put(2, new DbParameter(Types.BIGINT, rechargeOrder.getVipExp()));
		para.put(3, new DbParameter(Types.VARCHAR, rechargeOrder.getOrderNo()));

		return getDb().executeNoneQuery(sql, para) > -1;
	}

	private RechargeOrder initRechargeOrder(ResultSet rs) throws SQLException {
		RechargeOrder data = new RechargeOrder();
		data.setUserId(rs.getLong("userId"));
		data.setOrderNo(rs.getString("orderNo"));
		data.setAmount(rs.getLong("amount"));
		data.setOrderStatus(eOrderStatus.forValue(rs.getInt("orderStatus")));
		data.setMallId(rs.getInt("mallId"));
		data.setCreateTime(new Date(rs.getTimestamp("createTime").getTime()));
		data.setPreChargeType(rs.getInt("preChargeType"));
		data.setActivityId(rs.getInt("activityId"));
		data.setConditionId(rs.getInt("conditionId"));
		data.setPlatformOrderNo(rs.getString("platformOrderNo"));
		data.setSdkOrderNo(rs.getString("sdkOrderNo"));
		data.setReturnIngots(rs.getLong("returnIngots"));
		data.setAddVipExp(rs.getInt("addVipExp"));
		data.setShopType(rs.getInt("shopType"));
		data.setMallName(rs.getString("mallName"));
		data.setPayType(rs.getInt("payType"));
		data.setCurrency(rs.getString("currency"));
		data.setRealMoney(rs.getString("realMoney"));
		data.setRealCurrency(rs.getString("realCurrency"));
		data.setLocalMoney(rs.getString("localMoney"));
		data.setLocalCurrency(rs.getString("localCurrency"));
		data.setLtrRate(rs.getString("ltrRate"));
		data.setNewMoney(rs.getString("newMoney"));
		data.setTax(rs.getString("tax"));
		data.setProductCoin(rs.getLong("productCoin"));
		data.setDevice(rs.getInt("device"));
		data.setPayChannel(rs.getString("payChannel"));
		data.setOrderType(rs.getInt("orderType"));
		data.setCouponMoney(rs.getString("couponMoney"));
		data.setMallMarkId(rs.getInt("mallMarkId"));
		data.setRepeatBuy(rs.getBoolean("isRepeatBuy"));
		data.setProductType(rs.getString("productType"));
		data.setSubscription(rs.getString("subscription"));
		data.setCouponItemId(rs.getLong("couponItemId"));
		data.setCouponGoodsId(rs.getInt("couponGoodsId"));
		return data;
	}

	private static String getRechargeMultipleInfoSql = "select * from `t_s_recharge_multiple` where channelId = ?";
	public Map<Integer, RechargeMultipleInfo> getRechargeMultipleInfoList(int channelId) {
		Map<Integer, RechargeMultipleInfo> result = new ConcurrentHashMap<>();
		ResultSet rs = null;
		CallableStatement call = null;
		try {
			getDb().setConnName(DbNameStrategy.getServerDb());
			call = getDb().getConnection().prepareCall(getRechargeMultipleInfoSql);
			int i = 1;
			call.setInt(i++, channelId);
			rs = call.executeQuery();
			while (rs.next()) {
				RechargeMultipleInfo data = new RechargeMultipleInfo();
				data.setMallId(rs.getInt("mallId"));
				data.setMultipleList(StringUtils.stringToIntegerList(rs.getString("multipleList"), "\\|"));
				result.put(data.getMallId(), data);
			}
		} catch (Exception e) {
			logger.error(Thread.currentThread().getStackTrace()[1].getMethodName() + ":", e);
		} finally {
			getDb().closeConnection(call, rs);
		}
		return result;
	}

	public long getAllRechargeAmount(String dateBegin, String dateEnd) {
		long totalAmount = 0;
		ResultSet rs = null;
		CallableStatement call = null;
		String sql = "select SUM(amount) AS totalAmount FROM " +
				"(" +
				"select SUM(amount) AS `amount` FROM t_u_recharge_detail where createTime > ? and createTime < ? AND (orderStatus = 1 OR orderStatus = 2)  " +
				"UNION ALL " +
				"select SUM(amount) AS `amount` FROM t_u_thirdparty_recharge where createTime > ? and createTime < ? AND (orderStatus = 1 OR orderStatus = 2) " +
				") t ";
		try {
			getDb().setConnName(DbNameStrategy.getUserDb());
			call = getDb().getConnection().prepareCall(sql);
			call.setString(1, dateBegin);
			call.setString(2, dateEnd);
			call.setString(3, dateBegin);
			call.setString(4, dateEnd);
			rs = call.executeQuery();
			while (rs.next()) {
				totalAmount = rs.getLong("totalAmount");
			}
		} catch (SQLException e) {
			logger.error(Thread.currentThread().getStackTrace()[1].getMethodName() + ":", e);
		} finally {
			getDb().closeConnection(call, rs);
		}
		return totalAmount;
	}

	public boolean updateRechargeOrderRepeatBuy(RechargeOrder rechargeOrder) {
		String sql = "UPDATE t_u_recharge_detail SET isRepeatBuy=? WHERE orderNo=?";
		getDb().setConnName(DbNameStrategy.getUserDb());
		Map<Integer, DbParameter> para = new HashMap<>();
		para.put(1, new DbParameter(Types.BOOLEAN, rechargeOrder.isRepeatBuy()));
		para.put(2, new DbParameter(Types.VARCHAR, rechargeOrder.getOrderNo()));
		return getDb().executeNoneQuery(sql, para) > -1;
	}

	public boolean updateSubscribeRechargeOrder(RechargeOrder rechargeOrder) {
		String sql = "UPDATE t_u_recharge_detail SET orderStatus=?,updateTime=?,platformOrderNo=?,sdkOrderNo=?,payType=?,currency=?,realMoney=?,"
				+ "realCurrency=?,localMoney=?,localCurrency=?,ltrRate=?,newMoney=?,tax=?,productCoin=?,device=?,payChannel=?,orderType=?,"
				+ "couponMoney=?,productType=?,subscription=?,couponItemId=?,couponGoodsId=?,gid=?,appid=?,payMethod=?,packageId=? WHERE orderNo=?";
		getDb().setConnName(DbNameStrategy.getUserDb());
		Map<Integer, DbParameter> para = new HashMap<>();
		int i = 1;
		para.put(i++, new DbParameter(Types.INTEGER, rechargeOrder.getOrderStatus().getValue()));
		para.put(i++, new DbParameter(Types.TIMESTAMP, new Timestamp(System.currentTimeMillis())));
		para.put(i++, new DbParameter(Types.NVARCHAR, rechargeOrder.getPlatformOrderNo()));
		para.put(i++, new DbParameter(Types.NVARCHAR, rechargeOrder.getSdkOrderNo()));
		para.put(i++, new DbParameter(Types.INTEGER, rechargeOrder.getPayType()));
		para.put(i++, new DbParameter(Types.NVARCHAR, rechargeOrder.getCurrency()));
		para.put(i++, new DbParameter(Types.NVARCHAR, rechargeOrder.getRealMoney()));
		para.put(i++, new DbParameter(Types.NVARCHAR, rechargeOrder.getRealCurrency()));
		para.put(i++, new DbParameter(Types.NVARCHAR, rechargeOrder.getLocalMoney()));
		para.put(i++, new DbParameter(Types.NVARCHAR, rechargeOrder.getLocalCurrency()));
		para.put(i++, new DbParameter(Types.NVARCHAR, rechargeOrder.getLtrRate()));
		para.put(i++, new DbParameter(Types.NVARCHAR, rechargeOrder.getNewMoney()));
		para.put(i++, new DbParameter(Types.NVARCHAR, rechargeOrder.getTax()));
		para.put(i++, new DbParameter(Types.BIGINT, rechargeOrder.getProductCoin()));
		para.put(i++, new DbParameter(Types.INTEGER, rechargeOrder.getDevice()));
		para.put(i++, new DbParameter(Types.NVARCHAR, rechargeOrder.getPayChannel()));
		para.put(i++, new DbParameter(Types.INTEGER, rechargeOrder.getOrderType()));
		para.put(i++, new DbParameter(Types.VARCHAR, rechargeOrder.getCouponMoney()));
		para.put(i++, new DbParameter(Types.VARCHAR, rechargeOrder.getProductType()));
		para.put(i++, new DbParameter(Types.VARCHAR, rechargeOrder.getSubscription()));
		para.put(i++, new DbParameter(Types.BIGINT, rechargeOrder.getCouponItemId()));
        para.put(i++, new DbParameter(Types.BIGINT, rechargeOrder.getCouponGoodsId()));
		para.put(i++, new DbParameter(Types.VARCHAR, rechargeOrder.getGid()));
		para.put(i++, new DbParameter(Types.VARCHAR, rechargeOrder.getAppid()));
		para.put(i++, new DbParameter(Types.VARCHAR, rechargeOrder.getPayMethod()));
		para.put(i++, new DbParameter(Types.VARCHAR, rechargeOrder.getPackageId()));
		para.put(i++, new DbParameter(Types.VARCHAR, rechargeOrder.getOrderNo()));

		return getDb().executeNoneQuery(sql, para) > -1;
	}
}
